package com.coderlin.dao.user;

import com.coderlin.dao.BaseDao;
import com.coderlin.pojo.User;
import org.junit.platform.commons.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <Description> <br>
 *
 * @author Coder_Lin<br>
 * @version 1.0<br>
 * @taskId: <br>
 * @createDate 2021/03/11 22:36 <br>
 * @see com.coderlin.dao.user <br>
 */
public class UserDaoImpl implements UserDao{


    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        User user = null;
        String sql = "SELECT * FROM smbms_user where userCode=?";
        Object[] params = {userCode};

        if(connection!=null){
                rs = BaseDao.excute(connection,pstm,rs,sql,params);
                if(rs.next()){
                    user = new User();
                    //从数据库读数据进行赋值
                    user.setId(rs.getInt("id"));
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setUserPassword(rs.getString("userPassword"));
                    user.setGender(rs.getInt("gender"));
                    user.setBirthday(rs.getDate("birthday"));
                    user.setPhone(rs.getString("phone"));
                    user.setAddress(rs.getString("address"));
                    user.setUserRole(rs.getInt("userRole"));
                    user.setCreatedBy(rs.getInt("createdBy"));
                    user.setCreationDate(rs.getTimestamp("creationDate"));
                    user.setModifyBy(rs.getInt("modifyBy"));
                    user.setModifyDate(rs.getTimestamp("modifyDate"));


                }
                BaseDao.closeResource(connection,pstm,rs);

        }
        return user;
    }


    @Override
    public int updatePwd(Connection connection, int id, String userPassword) throws SQLException {
        
        PreparedStatement pstm = null;
        int excute = 0;
        String sql = "UPDATE smbms_user SET userPassword=? where id = ?;";
        Object[] params = {userPassword,id};

        if(connection!=null){
            excute = BaseDao.excute(connection, pstm, sql, params);

            //关闭资源
            BaseDao.closeResource(null,pstm,null);
        }

        return excute;
    }

    @Override
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;

        if(connection!=null){
            //sql字符串拼接sql查询语句
            StringBuffer sql = new StringBuffer();
            //list列表存储参数信息
            ArrayList<Object> list = new ArrayList<>();
            sql.append("SELECT count(1) as count FROM smbms_user u, smbms_role r where u.userRole=r.id");

            if(StringUtils.isNotBlank(username)){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            Object[] params = list.toArray();
            System.out.println("UserDaoImpl->getUserCount:" + sql.toString());
            rs = BaseDao.excute(connection, pstm, rs, sql.toString(), params);
            if(rs.next()){
                count = rs.getInt("count");
            }
        }

        return count;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<>();
        if(connection!=null){
            StringBuilder sql = new StringBuilder();
            ArrayList<Object> paramsList = new ArrayList<>();
            sql.append("SELECT u.* , r.roleName as userRoleName from smbms_user u, smbms_role r where u.userRole = r.id");
            if(StringUtils.isNotBlank(userName)){
                sql.append(" and u.userName like ?");
                paramsList.add("%"+userName+"%");
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                paramsList.add(userRole);
            }

            //对分页进行数据筛选
            sql.append(" order by u.creationDate DESC limit ?,?");
            currentPageNo = (currentPageNo-1)*pageSize;
            paramsList.add(currentPageNo);
            paramsList.add(pageSize);
            Object[] params = paramsList.toArray();
            System.out.println("UserDaoImpl->getUserCount: " + sql.toString());
            //执行
            rs = BaseDao.excute(connection, pstm, rs, sql.toString(), params);
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                //加入结果集
                userList.add(user);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return userList;
    }


}
